package com.oocl.euc.core.maintenance.repository;

import com.oocl.euc.core.maintenance.model.Function;
import com.oocl.euc.framework.base.persistence.IBaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface FunctionRepository extends IBaseRepository<Function,Long>{

    @Query(value = "SELECT DISTINCT F.* FROM PCRS_FUNCTIONS F LEFT JOIN PCRS_FUNCTION_AUTH FA ON F.ID = FA.FUNCTION_ID WHERE F.ACTIVE = 1 AND F" +
            ".STATUS_RELATED = 0 AND (F.ROLE_RELATED = 0 OR FA.ROLE_ID IN ?1)", nativeQuery = true)
    List<Function> findFunctionByRoleIds(List<Long> roleIds);

    @Query(value = "SELECT DISTINCT F.* FROM PCRS_FUNCTIONS F LEFT JOIN PCRS_FUNCTION_AUTH FA ON F.ID = FA.FUNCTION_ID WHERE F.ACTIVE = 1 AND F" +
            ".STATUS_RELATED = 0 AND F.ROLE_RELATED = 0", nativeQuery = true)
    List<Function> findFunctionNotRelatedWithRole();

    @Query(value = "SELECT DISTINCT F.* FROM PCRS_FUNCTIONS F LEFT JOIN PCRS_FUNCTION_AUTH FA ON F.ID = FA.FUNCTION_ID WHERE F.ACTIVE = 1 AND F" +
            ".STATUS_RELATED = 1 AND FA.REQUEST_STATUS_ID = ?1 AND (F.ROLE_RELATED = 0 OR FA.ROLE_ID IN ?2)", nativeQuery = true)
    List<Function> findFunctionByRoleIdsAndStatus(Long statusId, List<Long> roleIds);

    @Query(value = "SELECT DISTINCT F.* FROM PCRS_FUNCTIONS F LEFT JOIN PCRS_FUNCTION_AUTH FA ON F.ID = FA.FUNCTION_ID WHERE F.ACTIVE = 1 AND F" +
            ".STATUS_RELATED = 1 AND FA.REQUEST_STATUS_ID = ?1 AND F.ROLE_RELATED = 0", nativeQuery = true)
    List<Function> findFunctionByStatus(Long statusId);


}
